package com.koliko.from_now_on.dao.impl;

import com.koliko.from_now_on.bean.Team;
import com.koliko.from_now_on.dao.ITeamDao;
import com.koliko.from_now_on.utils.MyDBUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * dao实现
 */
public class TeamDaoImpl implements ITeamDao {
    public List<Team> list(Team team){
        QueryRunner queryRunner= MyDBUtils.getQueryRunner();
        String sql="SELECT (@i:=@i+1) team_count, s.*  FROM team s, (SELECT @i:=0) AS team_count ORDER BY team_title;";
        return getTeams(queryRunner, sql);
    }

    @Override
    public Integer save(Team team) {
        QueryRunner queryRunner = MyDBUtils.getQueryRunner();
        String sql = "Replace team(team_name,team_area,stu1_phone,stu1_id,stu2_id,stu3_id,stu1_name,stu2_name,stu3_name,stu1_class,stu2_class,stu3_class) value (?,?,?,?,?,?,?,?,?,?,?,?);";
        try {
            return queryRunner.update(sql,
                    team.getTeamName(),team.getTeamArea(),team.getStu1Phone(),
                    team.getStu1Id(),team.getStu2Id(),team.getStu3Id(),
                    team.getStu1Name(),team.getStu2Name(),team.getStu3Name(),
                    team.getStu1Class(),team.getStu2Class(),team.getStu3Class());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public List<Team> queryGJ(Team team){
        QueryRunner queryRunner= MyDBUtils.getQueryRunner();
        String sql="SELECT (@i:=@i+1) team_count, s.*  FROM team s, (SELECT @i:=0) AS team_count WHERE team_area=\"国际\" ORDER BY team_title;";
        return getTeams(queryRunner, sql);
    }
    public List<Team> queryDXC(Team team){
        QueryRunner queryRunner= MyDBUtils.getQueryRunner();
        String sql="SELECT (@i:=@i+1) team_count, s.*  FROM team s, (SELECT @i:=0) AS team_count WHERE team_area=\"大学城\" ORDER BY team_title;";
        return getTeams(queryRunner, sql);
    }
    public List<Team> queryWS(Team team){
        QueryRunner queryRunner= MyDBUtils.getQueryRunner();
        String sql="SELECT (@i:=@i+1) team_count, s.*  FROM team s, (SELECT @i:=0) AS team_count WHERE team_area=\"五山\" ORDER BY team_title;";
        return getTeams(queryRunner, sql);
    }

    @Override
    public Team checkTeam(String stu1Id) {
        QueryRunner queryRunner = MyDBUtils.getQueryRunner();
        String sql = "select * from team where stu1_id=? ";
        try {
            return queryRunner.query(sql, new ResultSetHandler<Team>() {
                @Override
                public Team handle(ResultSet resultSet) throws SQLException {
                    if(resultSet.next()){
                        Team team = new Team();
                        team.setTeamName(resultSet.getString("team_name"));
                        team.setTeamChoice(resultSet.getString("team_choice"));
                        team.setTeamTitle(resultSet.getString("team_title"));
                        team.setTeamArea(resultSet.getString("team_area"));
                        team.setStu1Phone(resultSet.getString("stu1_phone"));
                        team.setStu1Id(resultSet.getString("stu1_id"));
                        team.setStu1Name(resultSet.getString("stu1_name"));
                        team.setStu1Class(resultSet.getString("stu1_class"));
                        team.setStu2Id(resultSet.getString("stu2_id"));
                        team.setStu2Name(resultSet.getString("stu2_name"));
                        team.setStu2Class(resultSet.getString("stu2_class"));
                        team.setStu3Id(resultSet.getString("stu3_id"));
                        team.setStu3Name(resultSet.getString("stu3_name"));
                        team.setStu3Class(resultSet.getString("stu3_class"));
                        return team;
                    }
                    return null;
                }
            },stu1Id);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public Integer saveTitle(Team team) {
        QueryRunner queryRunner = MyDBUtils.getQueryRunner();
        String sql = "Replace team(team_title,team_choice,team_name,team_area,stu1_phone,stu1_id,stu2_id,stu3_id,stu1_name,stu2_name,stu3_name,stu1_class,stu2_class,stu3_class) value (?,?,?,?,?,?,?,?,?,?,?,?,?,?);";
        try {
            return queryRunner.update(sql,
                    team.getTeamTitle(),team.getTeamChoice(),
                    team.getTeamName(),team.getTeamArea(),team.getStu1Phone(),
                    team.getStu1Id(),team.getStu2Id(),team.getStu3Id(),
                    team.getStu1Name(),team.getStu2Name(),team.getStu3Name(),
                    team.getStu1Class(),team.getStu2Class(),team.getStu3Class());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

    }

    private List<Team> getTeams(QueryRunner queryRunner, String sql) {
        List<Team> list=null;
        try {
            list=queryRunner.query(sql, new ResultSetHandler<List<Team>>() {
                @Override
                public List<Team> handle(ResultSet resultSet) throws SQLException {
                    List<Team> list=new ArrayList<>();
                    while (resultSet.next())
                    {
                        Team team = new Team();
                        team.setTeamCount(resultSet.getInt("team_count"));
                        team.setTeamTitle(resultSet.getString("team_title"));
                        team.setTeamChoice(resultSet.getString("team_Choice"));
                        team.setTeamName(resultSet.getString("team_name"));
                        team.setTeamArea(resultSet.getString("team_area"));
                        team.setStu1Phone(resultSet.getString("stu1_phone"));
                        team.setStu1Id(resultSet.getString("stu1_id"));
                        team.setStu1Name(resultSet.getString("stu1_name"));
                        team.setStu1Class(resultSet.getString("stu1_class"));
                        team.setStu2Id(resultSet.getString("stu2_id"));
                        team.setStu2Name(resultSet.getString("stu2_name"));
                        team.setStu2Class(resultSet.getString("stu2_class"));
                        team.setStu3Id(resultSet.getString("stu3_id"));
                        team.setStu3Name(resultSet.getString("stu3_name"));
                        team.setStu3Class(resultSet.getString("stu3_class"));
                        list.add(team);
                    }
                    return list;
                }
            });
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

        return list;
    }


}


